CREATE PROCEDURE [dbo].[usp_AddMailMessage]
@sender varchar(256),
@fromName varchar(256),
@fromMailAddress varchar(256),
@toMailAddress varchar(1028),
@ccList varchar(1028),
@bccList varchar(1028),
@subject varchar(256),
@priority int,
@body varchar(8000),
@mailFormat int,
@label varchar(50),
@fk1 int,
@fk2 int,
@fk3 varchar(50)
as
begin
set nocount on
Insert into MailQueue(MailQueueID, Sender, FromName,FromMailAddress,ToMailAddress,CCList,BccList,[Subject],Body,Priority,MailFormat, ForeignKey1, ForeignKey2, ForeignKey3, Label, MailStatus, RetryCount)
Values(newid(), @sender, @fromName,@fromMailAddress,@toMailAddress,@ccList,@bccList,@subject,@body,@priority,@mailFormat, @fk1, @fk2, @fk3, @label, 1, 0)
end

GO

CREATE procedure [dbo].[usp_BeginMailProcess]
@ProcessId uniqueidentifier
as
begin
	UPDATE MailQueue SET MailProcessId = @ProcessId WHERE 5&MailStatus>0 AND MailProcessId is null and LEN(ToMailAddress) > 0
end

GO

CREATE procedure [dbo].[usp_GetMailMessage]
@ProcessId uniqueidentifier
as
begin
	set nocount on
	select * from MailQueue where MailStatus&8=0 and MailProcessId = @ProcessId and LEN(ToMailAddress) > 0
end

GO

CREATE PROCEDURE [dbo].[usp_PurgeOldQueueItems]
AS
BEGIN
	DECLARE @days as int
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT @days = [purgeafterdays] FROM Settings WHERE [iscurrentsetting] = 1
	
	if @days > 0
	BEGIN
		DELETE FROM MailQueue WHERE [MailCreateTime] < DATEADD ( day , -1*@days, getdate()) 
	END 
END

GO

CREATE procedure [dbo].[usp_UpdateMailMessage]
@MailQueueIDs  varchar(7000),
@status int
as
begin
            set nocount on

            declare @sql  varchar(8000)

            if @MailQueueIDs = ''
                   select @MailQueueIDs = null
            
            select @SQL = 'UPDATE MailQueue SET Mailstatus = ' + CAST(@status as varchar(10)) + ' WHERE MailQueueID IN (' + @MailQueueIDs + ')'
            --PRINT @SQL
            exec  (@SQL)
            
            if @status&4>0
            BEGIN
				select @SQL = 'UPDATE MailQueue SET RetryCount = RetryCount+1, MailProcessId = null WHERE MailQueueID IN (' + @MailQueueIDs + ')'
				exec  (@SQL)
            END
end

GO

CREATE PROCEDURE [dbo].[usp_UploadAttachment]
 @MailQueueId uniqueidentifier,
 @AttachmentName  varchar(300),
 @Attachment image
AS
insert into Attachments (MailQueueId, AttachmentId, AttachmentName, Attachment) values (@MailQueueId, newid(), @AttachmentName, @Attachment)

GO

CREATE PROCEDURE [dbo].[usp_DownloadAttachment]
 @AttachmentId uniqueidentifier
AS

select Attachment, AttachmentName
 from Attachments 
 where AttachmentId = @AttachmentId

GO